/*****************************************************************************
  Copyright ?2002-2004 by Martin Cook. All rights are reserved. If you like
  this code then feel free to go ahead and use it. The only thing I ask is 
  that you don't remove or alter my copyright notice. Your use of this 
  software is entirely at your own risk. I make no claims about the 
  reliability or fitness of this code for any particular purpose. If you 
  make changes or additions to this code then please clearly mark your code 
  as yours. If you have questions or comments then please contact me at: 
  martin@codegator.com
  
  Have Fun! :o)
*****************************************************************************/


using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

using Microsoft.ApplicationBlocks.Data.OleDb;

using CG.Security.Configuration;
using CG.Security.Data.Configuration;

namespace CG.Security.Data.Access
{
	
	/// <summary>
	/// An Access specific implementation of IRightData.
	/// </summary>
	internal sealed class RightData : IRightData
	{

		// *******************************************************************
		// Attributes.
		// *******************************************************************

		#region Attributes.

		/// <summary>
		/// The SQL to create a new right.
		/// </summary>
		private static readonly string SQL_CREATE = 
			"INSERT INTO cg_security_right (right_name, right_description) " +
			"VALUES(@right_name, @right_description)";

		/// <summary>
		/// The SQL to delete a right.
		/// </summary>
		private static readonly string SQL_DELETE = 
			"DELETE FROM cg_security_right " +
			"WHERE right_id = @right_id";

		/// <summary>
		/// The SQL to update a right.
		/// </summary>
		private static readonly string SQL_UPDATE = 
			"UPDATE cg_security_right " +
			"SET right_name = @right_name, " +
			"right_description = @right_description " +
			"WHERE right_id = @right_id";

		/// <summary>
		/// The SQL to find all rights.
		/// </summary>
		private static readonly string SQL_FIND_ALL = 
			"SELECT right_id, right_name, right_description " +
			"FROM cg_security_right " +
			"ORDER BY right_name";

		/// <summary>
		/// The SQL to find a right by primary key.
		/// </summary>
		private static readonly string SQL_FIND_BY_PK = 
			"SELECT right_id, right_name, right_description " +
			"FROM cg_security_right " +
			"WHERE right_id = @right_id";

		/// <summary>
		/// Various parameter names.
		/// </summary>
		private static readonly string PARM_RIGHT_ID = "@right_id";
		private static readonly string PARM_RIGHT_NAME = "@right_name";
		private static readonly string PARM_RIGHT_DESCRIPTION = "@right_description";
		private static readonly string PARM_TABLE_NAME = "cg_security_right";

		#endregion
		
		// ******************************************************************
		// IRightData implementation.
		// ******************************************************************

		#region IRightData implementation.

		/// <summary>
		/// Creates a new system-level right.
		/// </summary>
		/// <param name="rightName">The name of the right (must be unique).</param>
		/// <param name="rightDescription">The description of the right (optional).</param>
		/// <returns>The identifier for the new right.</returns>
		public int Create(
			string rightName,
			string rightDescription
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_CREATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_RIGHT_NAME, OleDbType.VarChar, 50),
					new OleDbParameter(PARM_RIGHT_DESCRIPTION, OleDbType.VarChar, 255)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_CREATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = rightName;
			parms[1].Value = rightDescription;

			OleDbConnection conn = null;

			try
			{

				// Create a new connection.
				conn = new OleDbConnection(
					settings.ConnectionString
					);
                
				// Open the connection.
				conn.Open();

				// Execute the SQL statement.
				OleDbHelper.ExecuteNonQuery(
					conn,
					CommandType.Text, 
					SQL_CREATE, 
					parms
					);

				// Return the identity value.
				return IdentityHelper.GetLastValue(
					conn, 
					PARM_TABLE_NAME
					);

			} // End try

			finally
			{

				// Should we close the connection?
				if (conn != null && conn.State != ConnectionState.Closed)
					conn.Close();

			} // End finally

		} // End Create()

		// ******************************************************************

		/// <summary>
		/// Deletes the specified system-level right.
		/// </summary>
		/// <param name="rightID">The identifier for the right.</param>
		public void Delete(
			int rightID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_DELETE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_DELETE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = rightID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_DELETE, 
				parms
				);

		} // End Delete()

		// ******************************************************************

		/// <summary>
		/// Updates the specified system-level right.
		/// </summary>
		/// <param name="rightID">The identifier for the right.</param>
		/// <param name="rightName">The new name for the right (must be unique).</param>
		/// <param name="rightDescription">The new description for the right (optional).</param>
		public void Update(
			int rightID,
			string rightName,
			string rightDescription
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_UPDATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_RIGHT_NAME, OleDbType.VarChar, 50),
					new OleDbParameter(PARM_RIGHT_DESCRIPTION, OleDbType.VarChar, 255),
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_UPDATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = rightName;
			parms[1].Value = rightDescription;
			parms[2].Value = rightID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_UPDATE, 
				parms
				);

		} // End Update()

		// ******************************************************************

		/// <summary>
		/// Returns all the system-level rights in the database.
		/// </summary>
		/// <returns>A DataSet containing all the rights in the database.</returns>
		public System.Data.DataSet FindAll()
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteDataset(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_ALL
				);

		} // End FindAll()

		// ******************************************************************

		/// <summary>
		/// Returns the properties for the specified system-level right.
		/// </summary>
		/// <param name="rightID">The identifier for the right.</param>
		/// <returns>A DataReader containing the properties for the specified right.</returns>
		public System.Data.IDataReader FindByPK(
			int rightID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_PK
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_PK, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = rightID;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteReader(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_PK, 
				parms
				);

		} // End FindByPK()

		#endregion 

	} // End class RightData

} // End namespace CG.Security.Data.Access

